const mysql = require('mysql2/promise'),
      request = require('koa2-request'),
      { con, ResData } = require('./common');

/*根据用户id查询用户健康数据*/
async function getHealthdataByUserid(userid){
  let res = new ResData();
  let sql = `select *
    from healthdata
    where user_id = ${userid};`;
  var [rows] = await con.execute(sql);
  res.data = rows;

  return res;
};
/*查找所有用户*/
async function getUsers(){
  let res = new ResData();
  let sql = `select comm.*, count(cares.user_id) as fans_count
from (
	select care.*, count(comm_id) as comm_count
	from  (
		select users.*, count(care_id) as care_count
		from users left join cares on users.user_id = cares.user_id
		group by users.user_id) as care left join community
	on care.user_id = community.user_id and care.user_id = care.user_id
	group by care.user_id) as comm left join cares
on comm.user_id = cares.care_id
group by comm.user_id;`;
  var [rows] = await con.execute(sql);
  res.data = rows;

  return res;
};
/*添加修改健康数据*/
async function addHealthdata(user_id,height,weight,bust,waistline,hipline,bmi){
  let res = new ResData();
  let sql = `insert into healthdata(user_id,height,weight,bust,waistline,hipline,bmi) 
    values ('${user_id}', '${height}', '${weight}', '${bust}', '${waistline}', '${hipline}', '${bmi}')
    on duplicate key update height= '${height}',weight='${weight}', bust='${bust}',waistline='${waistline}',hipline='${hipline}',bmi='${bmi}';`;
  console.log(sql);
  try {
    var [rows] = await con.execute(sql);
  } catch(e) {
    console.log(e.message);
    res.code = 40101;
    res.msg = 'insert failed';
  }
  return res;
}

/*添加用户（微信号，昵称，头像）*/
async function addUser(openid, user_name, user_avatar, user_sex){
  if(user_sex==1){
    user_sex='男'
  }else if(user_sex==2){
    user_sex='女'    
  }else{
    user_sex='--'
  }
  let res = new ResData();
  try {
    let sql = `INSERT INTO users(openid, user_name,user_avatar,user_sex) 
      VALUES ('${openid}', '${user_name}', '${user_avatar}', '${user_sex}');`;
    console.log(sql)
    let sql1 = `select * from users where openid = '${openid}';`
    let [rows] = await con.execute(sql1);
    console.log('rows');
    console.log(rows);
    if(rows.length == 0){
      await con.execute(sql);
      [rows] = await con.execute(sql1);
      res.data = rows[0].user_id;
    }else{
      res.data = rows[0].user_id;
    }
    console.log(res.data)
  } catch(e) {
    console.log(e.message);
    res.code = 40104;
    res.msg = 'insert failed';
  }
  return res;
}

/*通过用户id删除用户*/
async function deleteUserByUserid(user_id){
  let res = new ResData();
  let sql = `delete from users where user_id = ${user_id}`;
  console.log(sql);
  try {
    var [rows] = await con.execute(sql);
  } catch (e) {
    res.code = 401003;
    res.msg = 'delete failed';
  }
  return res;
};

/*根据用户id获取用户信息*/
async function getUserinfoByuserid(user_id){
  let res = new ResData();
  let sql = `select *
    from users
    where user_id = '${user_id}';`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  return res;
};

/*根据用户昵获取用户id*/
async function getUseridByUsername(user_name){
  let res = new ResData();
  let sql = `select user_id
    from users
    where user_name = '${user_name}';`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  return res;
};

/*查询关注列表*/
async function getCares(user_id){
  let res = new ResData();
  let sql = `select cares.user_id, care_id, user_name, user_avatar
    from cares,users
    where cares.care_id=users.user_id and cares.user_id = ${user_id};`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  return res;
};
/*查询粉丝列表*/
async function getFans(user_id){
  let res = new ResData();
  let sql = `select cares.user_id, care_id, user_name, user_avatar
    from cares,users
    where cares.user_id=users.user_id and care_id = ${user_id};`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  return res;
};

/*添加关注*/
async function toCare(user_id, care_id){
  let res = new ResData();
  let sql = `insert into cares(user_id, care_id)
    values ('${user_id}', '${care_id}');`;
    console.log(sql)
  try{
    var [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401004;
    res.msg = 'insert failed';
  }
  return res;
};
/*取消关注*/
async function delCare(user_id, care_id){
  let res = new ResData();
  let sql = `delete from cares 
    where user_id = '${user_id}' and care_id = '${care_id}';`;
    console.log(sql)
  try {
    var [rows] = await con.execute(sql);
  } catch (e) {
    res.code = 401004;
    res.msg = 'delete failed';
  }
  return res;
};
/*用户添加课程*/
async function addCourse(user_id, course_id){
  let res = new ResData();
  let sql = `insert into usercourses(user_id, course_id) values ('${user_id}', '${course_id}');`;
  try{
    var [rows] = await con.execute(sql);
    res.msg = rows.affectedRows === 1 ? 'ok' : 'insert failed';
  }catch(e){
    res.code = 401004;
    res.msg = 'insert failed';
  }
  return res;
};
/*取消课程*/
async function delCourse(user_id, course_id){
  let res = new ResData();
  let sql = `delete from usercourses 
    where user_id=${user_id} and course_id = ${course_id};`;
  try {
    var [rows] = await con.execute(sql);
    res.msg = rows.affectedRows === 1 ? 'ok' : 'delete failed';
  } catch (e) {
    res.code = 401004;
    res.msg = 'delete failed';
  }
  return res;
};
/*查询个人动态*/
async function getUsercommByUserid(user_id){
  let res = new ResData();
  let sql = `select comm_id, comm_text, comm_time, community.user_id, user_name, user_avatar 
    from users, community
    where users.user_id = community.user_id and users.user_id = ${user_id}
    order by comm_id desc;`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  return res;
};
/*获取运动数据*/
async function getUserSportdataByUserid(user_id){
  let res = new ResData();
  let sql = `select * from sportdata
where user_id = ${user_id}
order by sport_id desc;`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  return res;
};
/*添加运动数据*/
async function addSportdata(user_id, sport_distance, sport_time, sport_date){
  let res = new ResData();
  let sql = `insert into sportdata(user_id, sport_distance, sport_time, sport_date)
values (${user_id}, '${sport_distance}', '${sport_time}', '${sport_date}');`;
  console.log(sql);
  try{
    var [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401004;
    res.msg = 'insert failed';
  }
  return res;
};
/*登录校验*/
async function login(code){
  try{
    let appId = 'wxaa7176fb9a7e7898';
    let secret = '41d02805a01330be925df713a00a2a1d';
    let qres = await await request({
      url: `https://api.weixin.qq.com/sns/jscode2session?appid=${appId}&secret=${secret}&js_code=${code}&grant_type=authorization_code`
    })
    let body = JSON.parse(qres.body);
    return body;
  }catch(e){
    console.log(e);
    return 'failed';
  }


}
module.exports = {
  getHealthdataByUserid,
  addHealthdata,
  addUser,
  deleteUserByUserid,
  getUseridByUsername,
  getCares,
  getFans,
  toCare,
  delCare,
  getUsers,
  addCourse,
  delCourse,
  getUsercommByUserid,
  getUserSportdataByUserid,
  addSportdata,
  login,
  getUserinfoByuserid
};
